﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using Dapper;
using DapperExtensions;
using DapperExtensions.Mapper;


namespace ZilLion.Service.Core.DapperWrapper
{
    public class DapperHelper
    {
        /// <summary>
        ///     执行
        /// </summary>
        /// <param name="action"></param>
        /// <param name="context"></param>
        public static void NoLockInvoke(Action<IDbConnection, IDbTransaction> action, IDbContext context = null)
        {
            if (context == null) throw new Exception("dapper操作的context不可以为空");
            using (var connection = context.GetConnection())
            {
                try
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        action(connection, transaction);
                        transaction.Commit();
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        #region 增删改查

        /// <summary>
        ///     根据Map 生成查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <returns></returns>
        public static string CreateSqlByMap<T>(string table = null)
            where T : class
        {
            var map = DapperExtensions.DapperExtensions.GetMap<T>() as ClassMapper<T>;
            var tablename = table;
            var mapTable = map?.TableName;
            if (mapTable.IsNotNullOrEmpty())
                tablename = mapTable;
            return $"SELECT {CreateColSqlByMap<T>()} FROM {tablename}";
        }

        /// <summary>
        ///     根据map 获取所有列的SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static string CreateColSqlByMap<T>(string pix = "")
            where T : class
        {
            var map = DapperExtensions.DapperExtensions.GetMap<T>() as ClassMapper<T>;
            var querysql = string.Empty;
            var cols = map?.Properties;
            if (cols != null)
                querysql = cols.Aggregate(querysql,
                    (current, col) =>
                        current +
                        $"{(pix.IsNotNullOrEmpty() ? pix + "." : string.Empty)}{col.ColumnName} AS {col.Name} ,");
            else
                querysql = querysql + $"{(pix.IsNotNullOrEmpty() ? pix + "." : string.Empty)}*";
            if (querysql.EndsWith(","))
                querysql = querysql.Left(querysql.Length - 1);

            return querysql;
        }

        /// <summary>
        ///     执行SQL,映射实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="unitOfWork"></param>
        /// <param name="parameters"></param>
        /// <param name="context"></param>
        public static void Execute(string sql, IDbContext context = null, UnitOfWork unitOfWork = null,
            dynamic parameters = null)
        {
            if (context == null) throw new Exception("dapper操作的context不可以为空");

            Func<IDbConnection, IDbTransaction, object, object> action = null;

            if (unitOfWork == null ||
                unitOfWork.Option == UnitOfWorkOption.Suppress)
            {
                var writeConnection = context.GetConnection();
                try
                {
                    writeConnection.Open();
                    SqlMapper.Execute(writeConnection, sql, parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    writeConnection.Close();
                }
            }
            if (unitOfWork == null) return;
            action = (conn, tran, entity) => SqlMapper.Execute(conn, sql, parameters, tran);
            unitOfWork.ChangeOperator(action, context);
        }

        /// <summary>
        ///     获取所有实体
        /// </summary>
        /// <returns></returns>
        public static IEnumerable<T> GetAllModel<T>(object predicate = null, IDbContext context = null)
            where T : class, new()
        {
            IEnumerable<T> list = null;
            NoLockInvoke(
                delegate(IDbConnection conn, IDbTransaction tran)
                {
                    list = conn.GetList<T>(predicate, null, tran, null, false);
                }, context);
            return list;
        }

        /// <summary>
        ///     判断是否是改变数据库的SQL dcl
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool IsChangeSqlString(string sql)
        {
            sql = sql.Trim().ToLower();
            return Regex.IsMatch(sql, "^(update|delete|insert|create|alter|drop|truncate)") ||
                   Regex.IsMatch(sql, @"^(select)\s+(into)\s");
        }

        /// <summary>
        ///     根据SQL获取实体列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static IEnumerable<T> GetList<T>(string sql, IDbContext context = null, dynamic parameters = null)
        {
            if (IsChangeSqlString(sql))
                throw new Exception("sql语句错误,执行的是查询方法,但SQL语句包含更新代码,SQL语句:" + sql);
            IEnumerable<T> list = null;
            NoLockInvoke(
                delegate(IDbConnection conn, IDbTransaction tran)
                {
                    list = (IEnumerable<T>) SqlMapper.Query<T>(conn, sql, parameters, tran);
                }, context);
            return list;
        }

        /// <summary>
        ///     删除实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <param name="context"></param>
        public static void RemoveModel<T>(T item, UnitOfWork unitWork = null, IDbContext context = null)
            where T : class, new()
        {
            if (context == null) throw new Exception("dapper操作的context不可以为空");

            if (unitWork == null ||
                unitWork.Option == UnitOfWorkOption.Suppress)
            {
                var writeConnection = context.GetConnection();
                try
                {
                    writeConnection.Open();
                    writeConnection.Delete(item, null, null);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    writeConnection.Close();
                }
            }
            else
            {
                Func<IDbConnection, IDbTransaction, object, object> action =
                    (conn, tran, entity) => conn.Delete(item, tran, null);
                unitWork.ChangeOperator(action, context);
            }
        }

        /// <summary>
        ///     更新实体
        /// </summary>
        /// <param name="item"></param>
        /// <param name="unitWork"></param>
        /// <param name="context"></param>
        public static void ModifyModel<T>(T item, UnitOfWork unitWork = null, IDbContext context = null)
            where T : class, new()
        {
            if (context == null) throw new Exception("dapper操作的context不可以为空");

            if (unitWork == null ||
                unitWork.Option == UnitOfWorkOption.Suppress)
            {
                var writeConnection = context.GetConnection();
                try
                {
                    writeConnection.Open();
                    writeConnection.Update(item, null, null);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    writeConnection.Close();
                }
            }
            else
            {
                Func<IDbConnection, IDbTransaction, object, object> action =
                    (conn, tran, entity) => conn.Update(item, tran, null);
                unitWork.ChangeOperator(action, context);
            }
        }

        /// <summary>
        ///     获取单个标量值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="context"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static T? GetDataByScalar<T>(string sql, IDbContext context = null, dynamic parameters = null)
            where T : struct
        {
            if (IsChangeSqlString(sql))
                throw new Exception("sql语句错误,执行的是查询方法,但SQL语句包含更新代码,SQL语句:" + sql);
            T? value = null;
            var type = typeof(T);
            NoLockInvoke(delegate(IDbConnection conn, IDbTransaction tran)
            {
                IEnumerable<object> values = SqlMapper.Query(conn, type, sql, parameters, tran);
                value = (T?) values.SingleOrDefault();
            }, context);
            return value;
        }

        public static string GetDataByScalarString(string sql, IDbContext context = null, dynamic parameters = null)
        {
            if (IsChangeSqlString(sql))
                throw new Exception("sql语句错误,执行的是查询方法,但SQL语句包含更新代码,SQL语句:" + sql);
            string value = null;
            var type = typeof(string);
            NoLockInvoke(delegate(IDbConnection conn, IDbTransaction tran)
            {
                IEnumerable<object> values = SqlMapper.Query(conn, type, sql, parameters, tran);
                var singleOrDefault = values.SingleOrDefault();
                if (singleOrDefault != null) value = singleOrDefault.ToString();
            }, context);
            return value;
        }

        /// <summary>
        ///     添加实体
        /// </summary>
        /// <param name="item"></param>
        /// <param name="context"></param>
        /// <param name="unitWork"></param>
        /// <returns></returns>
        public static dynamic AddModel<T>(T item, IDbContext context = null,
            UnitOfWork unitWork = null) where T : class, new()
        {
            if (context == null) throw new Exception("dapper操作的context不可以为空");

            if (unitWork == null ||
                unitWork.Option == UnitOfWorkOption.Suppress)
            {
                var writeConnection = context.GetConnection();
                object obj2 = null;
                try
                {
                    writeConnection.Open();
                    obj2 = writeConnection.Insert(item, null, null);
                }
                finally
                {
                    writeConnection.Close();
                }
                return obj2;
            }
            {
                Func<IDbConnection, IDbTransaction, object, object> action =
                    (conn, tran, oitem) => conn.Insert((T) oitem, tran, null);
                var mapper = DapperExtensions.DapperExtensions.GetMap<T>();
                var properties = mapper.Properties;
                var propertyMap =
                    properties.Where(p => p.Name == "Id").SingleOrDefault(p => p.ColumnName.IndexOf('.') != -1);
                unitWork.ChangeOperator(action, context, item, propertyMap);
                return null;
            }
        }

        #endregion
    }
}